package com.example.demo.utils;

import java.io.*;

import java.sql.*;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * ClassName: JdbcUtil
 * Description: TODO
 * User: kevin
 * Date: 2018-11-15
 * Time: 下午7:02
 */
public class JdbcUtil {

    //数据库地址
    private String URL;

    //数据库用户名
    private String NAME;

    //数据库密码
    private String PASSWD;

    //数据库驱动
    private String DRIVER;

    private Connection connection=null;

    private PreparedStatement preparedStatement = null;

    private ResultSet resultSet = null;

    public JdbcUtil(){

        //从文件中获取参数
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream("mysql.properties");;
        Properties properties = new Properties();

        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //设置参数
        this.URL = properties.getProperty("URL");
        this.NAME = properties.getProperty("NAME");
        this.PASSWD = properties.getProperty("PASSWD");
        this.DRIVER = properties.getProperty("DRIVER");

        System.out.println(this.URL);
        System.out.println(this.NAME);
        System.out.println(this.PASSWD);
        System.out.println(this.DRIVER);
        try {
            Class.forName(this.DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * @Auther: kevin
     * @param
     * @return: java.sql.Connection
     * @Descriptions:建立数据库连接
     * @date: 18-11-15 下午8:12
     */
    public Connection getConnection(){

        try {
            this.connection = DriverManager.getConnection(this.URL,this.NAME,this.PASSWD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return this.connection;
    }

    /**
     * @Auther: kevin
     * @param sql
    * @param parameters
     * @return:
     * @Descriptions:查询数据　
     * @date: 18-11-15 下午8:34
     */
    public List<Map<String,String>> executeQuery(String sql, String parameters[]) throws SQLException {
        if(parameters==null || parameters.length==0){
            System.out.println("参数出错！");
            return null;
        }

        List<Map<String,String>> resultList = new ArrayList<Map<String,String>>();

        preparedStatement = connection.prepareStatement(sql);

        for(int i=0;i<parameters.length;i++) {

            preparedStatement.setString(i + 1, parameters[i]);System.out.println(parameters[i]);

        }
        //得到结果集

        resultSet = preparedStatement.executeQuery();

        /*
        * 封装map集合
        * */
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        //获取字段数
        int col_count = resultSetMetaData.getColumnCount();

        while(resultSet.next()){

            Map<String,String> resultMap = new HashMap<>();
            for(int i=0;i<col_count;i++){

                //获取字段名称，值
                String key = resultSetMetaData.getColumnName(i+1);
                String value = resultSet.getString(key);
                //存入map
                resultMap.put(key,value);
            }
            resultList.add(resultMap);
        }


        return resultList;
    }

    /**
     * @Auther: kevin
     * @param sql
    * @param parameters
     * @return: void
     * @Descriptions:增删改数据
     * @date: 18-11-15 下午8:34
     */
    public void execute(String sql, String parameters[]) throws SQLException {

        if(parameters==null && parameters.length==0){
            System.out.println("参数错误！");
            return;
        }
        preparedStatement = connection.prepareStatement(sql);

        for(int i=0;i<parameters.length;i++) {

            preparedStatement.setString(i + 1, parameters[i]);

        }

        preparedStatement.executeUpdate();

    }


    public void closeAll(){
        closeConnection();
        closePreparedStatement();
        closeResultSet();
    }
    /**
     * @Auther: kevin
     * @param
     * @return: void
     * @Descriptions:关闭数据库连接
     * @date: 18-11-15 下午8:16
     */
    public void closeConnection(){


        try {
            if(this.connection!=null) this.connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * @Auther: kevin
     * @param
     * @return: void
     * @Descriptions:关闭　PreparedStatement　连接
     * @date: 18-11-15 下午8:33
     */
    public void closePreparedStatement(){


        try {
            if(this.preparedStatement!=null) this.preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * @Auther: kevin
     * @param
     * @return: void
     * @Descriptions:关闭结果集
     * @date: 18-11-15 下午9:01
     */
    public void closeResultSet(){

        try {
            if(this.resultSet!=null) this.resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
